package com.bangf.repo;

import com.bangf.domain.FpHousehold;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import javax.transaction.Transactional;
import java.util.List;

public interface HouseholdRepo extends JpaRepository<FpHousehold,Integer> {


    @Query(value =  "SELECT * FROM fp_household " +
                    "WHERE town_id = ?1",nativeQuery = true)
    List<FpHousehold> findByTownId(Integer townId);


    @Query(value =  "SELECT * FROM fp_household",nativeQuery = true)
    List<FpHousehold> findAllHouseHold();

    @Query(value =  "SELECT * FROM fp_household " +
                    "limit ?1,?2",nativeQuery = true)
    List<FpHousehold> findLimit(Integer index, Integer limit);


    @Transactional
    @Modifying
    @Query(value =  "UPDATE fp_household as fh SET fh.all_income = " +
                    "fh.per_income * ?2 WHERE id = ?1",nativeQuery = true)
    void updateAllIncome(Integer householdId,Integer manNumber);


    @Query(value =  "SELECT sum(fh.all_income) FROM fp_household as fh " +
                    "WHERE town_id = ?1",nativeQuery = true)
    Integer sumAllIncomeByTownId(Integer townId);

    @Query(value =  "SELECT sum(fh.all_income) FROM fp_household as fh " +
                    "WHERE village_id = ?1",nativeQuery = true)
    Integer sumAllIncomeByVillageId(Integer villageId);

    @Query(value =  "SELECT count(*) FROM fp_household as fh " +
                    "WHERE town_id = ?1",nativeQuery = true)
    Integer countAllIncomeByTownId(Integer townId);

    @Transactional
    @Modifying
    @Query(value =  "UPDATE fp_household as fh SET fh.is_wb = 1 " +
                    "WHERE id = ?1",nativeQuery = true)
    void updateWb(Integer householdId);

    @Query(value =  "SELECT count(*) FROM fp_household as fh " +
                    "WHERE fh.helper != \"\" AND " +
                    "fh.town_id = ?1",nativeQuery = true)
    Integer countHelperByTownId(Integer townId);


    @Query(value =  "SELECT count(*) FROM fp_household as fh " +
                    "WHERE fh.nh_type = 2 AND " +
                    "fh.town_id = ?1",nativeQuery = true)
    Integer countCardByTownId(Integer townId);


    @Query(value =  "SELECT sum(fh.per_income) FROM fp_household as fh " +
                    "WHERE fh.nh_type = ?1 " +
                    "AND fh.town_id = ?2",nativeQuery = true)
    Integer sumAllIncomeByNhTypeAndTownId(Integer nhType, Integer townId);


    @Query(value =  "SELECT count(*) FROM fp_household as fh " +
                    "WHERE fh.nh_type = ?1 " +
                    "AND fh.town_id = ?2",nativeQuery = true)
    Integer countAllIncomeByNhTypeAndTownId(Integer nhType, Integer townId);

    @Query(value =  "select * from fp_household as fh " +
                    "where hz_name = ?1",nativeQuery = true)
    List<FpHousehold> findByHzName(String name);

    @Query(value =  "select * from fp_household as fh " +
                    "where hz_idcard = ?1",nativeQuery = true)
    List<FpHousehold> findByIdCard(String idCard);
}
